﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using WebAPI.Entities;

namespace WebAPI.Dao;

public class ArticleMapper
{
    private SqlCommandExecutor _sqlCommandExecutor = new SqlCommandExecutor();
    private TimeTransferUtils _transferUtils = new TimeTransferUtils();
    
    public List<Article> SelectAll()
    {
        List<Article> list = new List<Article>();
        Article article = null;

        string sql = "select * from [article];";
        SqlDataReader sqlDataReader = _sqlCommandExecutor.Execute(sql);
        try
        {
            while (sqlDataReader.Read())
            {
                string id = (string)sqlDataReader["id"];
                string user_id = (string)sqlDataReader["user_id"];
                string title = (string)sqlDataReader["title"];
                string inner_text = (string)sqlDataReader["inner_text"];
                string tag = (string)sqlDataReader["tag"];
                long star = (long)sqlDataReader["star"];
                long watcher = (long)sqlDataReader["watcher"];
                string create_time = _transferUtils.GetTimeString((DateTime)sqlDataReader["create_time"]);
                string update_time = _transferUtils.GetTimeString((DateTime)sqlDataReader["update_time"]);
            
                article = new Article(id, user_id, title, inner_text, tag, star, watcher, create_time, update_time);
                list.Add(article);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
        finally
        {
            _sqlCommandExecutor.CloseConnection();
        }
        
        return list;
    }
    
    public List<Article> SelectAll(string userId)
    {
        List<Article> list = new List<Article>();
        Article article = null;

        string sql = "select * from [article] where user_id='" + userId + "';";
        SqlDataReader sqlDataReader = _sqlCommandExecutor.Execute(sql);
        try
        {
            while (sqlDataReader.Read())
            {
                string id = (string)sqlDataReader["id"];
                string user_id = (string)sqlDataReader["user_id"];
                string title = (string)sqlDataReader["title"];
                string inner_text = (string)sqlDataReader["inner_text"];
                string tag = (string)sqlDataReader["tag"];
                long star = (long)sqlDataReader["star"];
                long watcher = (long)sqlDataReader["watcher"];
                string create_time = _transferUtils.GetTimeString((DateTime)sqlDataReader["create_time"]);
                string update_time = _transferUtils.GetTimeString((DateTime)sqlDataReader["update_time"]);
            
                article = new Article(id, user_id, title, inner_text, tag, star, watcher, create_time, update_time);
                list.Add(article);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
        finally
        {
            _sqlCommandExecutor.CloseConnection();
        }
        
        return list;
    }
    
    public Article SelectOne(string _id)
    {
        Article article = null;
        string sql = "select * from [article] where id='" + _id +"';";

        SqlDataReader sqlDataReader = _sqlCommandExecutor.Execute(sql);
        try
        {
            while (sqlDataReader.HasRows && sqlDataReader.Read())
            {
                string id = (string)sqlDataReader["id"];
                string user_id = (string)sqlDataReader["user_id"];
                string title = (string)sqlDataReader["title"];
                string inner_text = (string)sqlDataReader["inner_text"];
                string tag = (string)sqlDataReader["tag"];
                long star = (long)sqlDataReader["star"];
                long watcher = (long)sqlDataReader["watcher"];
                string create_time = _transferUtils.GetTimeString((DateTime)sqlDataReader["create_time"]);
                string update_time = _transferUtils.GetTimeString((DateTime)sqlDataReader["update_time"]);

                article = new Article(id, user_id, title, inner_text, tag, star, watcher, create_time, update_time);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
        finally
        {
            _sqlCommandExecutor.CloseConnection();
        }
        
        return article;
    }
    
    
    public int Insert(Article article)
    {
        string sql = "insert into [article](id, user_id, title, inner_text, tag, star, watcher, create_time, update_time) values(@id, @user_id, @title, @inner_text, @tag, @star, @watcher, @create_time, @update_time);";

        SqlCommand sqlCommand = _sqlCommandExecutor.PrepareSqlCommand(sql);
        
        sqlCommand.Parameters.AddWithValue("id", article.Id);
        sqlCommand.Parameters.AddWithValue("user_id", article.UserId);
        sqlCommand.Parameters.AddWithValue("title", article.Title);
        sqlCommand.Parameters.AddWithValue("inner_text", article.InnerText);
        sqlCommand.Parameters.AddWithValue("tag", article.Tag);
        sqlCommand.Parameters.AddWithValue("star", article.Star);
        sqlCommand.Parameters.AddWithValue("watcher", article.Watcher);
        sqlCommand.Parameters.AddWithValue("create_time", article.CreateTime);
        sqlCommand.Parameters.AddWithValue("update_time", article.UpdateTime);
        
        int flag = 0;
        
        try
        {
            flag = sqlCommand.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
        finally
        {
            _sqlCommandExecutor.CloseConnection();
        }
        
        return flag;
    }
    
    public int Update(Article article)
    {
        string sql = "update [article] set id=@id, user_id=@user_id, title=@title, inner_text=@inner_text, tag=@tag, star=@star, watcher=@watcher, update_time=@update_time;";

        SqlCommand sqlCommand = _sqlCommandExecutor.PrepareSqlCommand(sql);
        
        sqlCommand.Parameters.AddWithValue("id", article.Id);
        sqlCommand.Parameters.AddWithValue("user_id", article.UserId);
        sqlCommand.Parameters.AddWithValue("title", article.Title);
        sqlCommand.Parameters.AddWithValue("inner_text", article.InnerText);
        sqlCommand.Parameters.AddWithValue("tag", article.Tag);
        sqlCommand.Parameters.AddWithValue("star", article.Star);
        sqlCommand.Parameters.AddWithValue("watcher", article.Watcher);
        sqlCommand.Parameters.AddWithValue("update_time", article.UpdateTime);
        
        int flag = 0;
        
        try
        {
            flag = sqlCommand.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
        finally
        {
            _sqlCommandExecutor.CloseConnection();
        }
        
        return flag;
    }
    
    public int Delete(Article article)
    {
        string sql = "delete from [article] where id=@id;";

        SqlCommand sqlCommand = _sqlCommandExecutor.PrepareSqlCommand(sql);
        
        sqlCommand.Parameters.AddWithValue("id", article.Id);
        
        int flag = 0;
        
        try
        {
            flag = sqlCommand.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
        finally
        {
            _sqlCommandExecutor.CloseConnection();
        }
        
        return flag;
    }
}